

project , uses(../data_output/legislators_klarner.dta)
project , original(../data_input/CO/CO_bills_1997.txt)
project , original(../data_input/CO/CO_bills_1998.txt)
project , original(../data_input/CO/CO_bills_1999.txt)
project , original(../data_input/CO/co_bills.csv)	


	**********
	** Colorado House
	** Consecutive term limites: 4 x 2-year terms.
	** First year: 1998	
	**********
	
	use ../data_output/legislators_klarner.dta, clear
	keep if state=="CO" & chamber=="house"
	keep if regexm(term, "1997|1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen lastname = trim(regexs(1)) if regexm(lower(CandName),"([^,]+)")
	gen firstname = trim(regexs(1)) if regexm(lower(CandName),",( [^ ])")
	gen mergename = lastname
	bys lastname term: gen n = _n
	by lastname term: egen mean_n=mean(n)
	replace mergename =  lastname + " " + firstname+"." if mean_n>1 //firstname+ ". "
	save ../data_output/legislators_CO_house_1997_2016.dta, replace
	
	gen year_minus0 = year	
	gen year_minus2 = year-2
	gen year_minus4 = year-4
	gen year_minus6 = year-6
	tostring year_minus*, replace
	gen s = year_minus6+";" +year_minus4+";"+year_minus2+";"+ year_minus0 + ";"
	gen termlimited = regexm(elections_won, s)==1 & year>=1996
	save ../data_output/legislators_CO_house_1997_2016.dta, replace
	
project , creates(../data_output/legislators_CO_house_1997_2016.dta)
	
	
	//bills
	clear
	gen sponsor = ""
	tempfile co
	save `co', replace
	foreach year in 1997 1998 1999 {
		import delimited using ../data_input/CO/CO_bills_`year'.txt,  clear  delimiters(tab) bindquotes(strict) encoding("utf-8") varnames(1)
		keep bill sponsor subject
		gen session = "`year'"
		append using `co'
		save `co', replace
	}	

	import delimited using ../data_input/CO/co_bills.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	tostring session, replace
	append using `co' 
	drop if sponsor=="" | sponsor=="(NONE)" | regexm(sponsor, "[.][.][.]")
	replace sponsor = lower(sponsor)
	gen mergename = sponsor
	replace mergename = regexs(1) if regexm(mergename, "([^ ]+)$") & session=="1997"
	gen term = ""
	replace term = "1997-1998" if regexm(session, "1997|1998")
	replace term = "1999-2000" if regexm(session, "1999|2000")
	replace term = "2001-2002" if regexm(session, "2001|2002")
	replace term = "2003-2004" if regexm(session, "2003|2004")
	replace term = "2005-2006" if regexm(session, "2005|2006")
	replace term = "2007-2008" if regexm(session, "2007|2008")
	replace term = "2009-2010" if regexm(session, "2009|2010")
	replace term = "2011-2012" if regexm(session, "2011|2012")
	replace term = "2013-2014" if regexm(session, "2013|2014")
	replace term = "2015-2016" if regexm(session, "2015|2016")
	replace mergename = "bush" if mergename=="mitsch bush"
	replace mergename = "gardner r." if mergename=="gardner b."
	replace mergename = "may" if mergename=="may m."
	replace mergename = "navarroratzlaff"  if mergename=="navarro"
	replace mergename = "schafer"  if mergename=="schafer s."
	replace mergename = "vanwinkle"  if mergename=="van winkle"
	replace mergename = "krafttharp"  if mergename=="kraft-tharp"
	replace mergename = "carroll"  if mergename=="carroll t." & term=="2009-2010"
	replace mergename = "berry c."  if mergename=="c. berry" 
	replace mergename = "berry g."  if mergename=="g. berry"  | mergename=="g.berry"
	replace mergename = "williams t."  if mergename=="t. williams" 
	replace mergename = "williams s."  if mergename=="s. williams" 
	replace mergename = "williams"  if mergename=="williams a." & term=="2011-2012"
	replace mergename = "williams s."  if mergename=="swilliams" 
	replace mergename = "williams t."  if mergename=="twilliams" 
	replace mergename = "williams"  if mergename=="williams s."  & term=="2011-2012"
	replace mergename = "mitchell"  if mergename=="mitchell v." 
	replace mergename = "johnson"  if mergename=="s. johnson" & term=="1997-1998"
	replace mergename = "johnson"  if mergename=="johnson r." & term=="2003-2004"
	replace mergename = "king"  if mergename=="king s." & term=="2009-2010"
	replace mergename = "neville"  if mergename=="neville p." & term=="2015-2016"
	
	merge m:1 mergename term using ../data_output/legislators_CO_house_1997_2016.dta
	//5 observations are not merging
	drop if _merge==1
	drop sponsor
	gen sponsor = 1 if bill!=""
	gen  law = regexm(status, "governor signed") 
	gen billlength = length(billtext)
	gen sponsor_approp = regexm(cmt_assign, "APP|FIN")
	save ../data_output/bills_by_sponsor_CO_1997_2016.dta, replace
project , creates(../data_output/bills_by_sponsor_CO_1997_2016.dta)	
	
	use ../data_output/bills_by_sponsor_CO_1997_2016.dta, clear
	collapse (sum) billlength sponsor* law (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party mergename inoffice year termlimited vtsh, by(CandId term )
	replace law = . if term == "1997-1998"
	replace billlength = . if term == "1997-1998"
	replace sponsor_approp = . if term == "1997-1998"

	save ../data_output/total_bills_by_sponsor_CO_1997_2016.dta, replace
project , creates(../data_output/total_bills_by_sponsor_CO_1997_2016.dta)	
	
	
	
	** Floor votes

	use ../data_output/legislators_klarner.dta, clear
	gen lastname = regexs(1) if regexm(lower(CandName),"([^,]+), [^!]")
	gen firstname = regexs(1) if regexm(lower(CandName),"[^,]+, ([^ ])")
	keep if state=="CO" & chamber=="house"
	keep if regexm(term, "2003|2005|2007|2009|2011|2013|2015")
	gen os_mergename = lastname
	bys  os_mergename term: gen n = _n
	by  os_mergename term: egen mean_n=mean(n)
	replace os_mergename = lastname + " " + firstname  if mean_n>1  //firstname  + " " +  lastname //firstname+ ". "
	drop n mean_n
	tempfile tmp
	save `tmp', replace
	


project , original(../data_input/CO/co_votes.csv)	
	import delimited using ../data_input/CO/co_votes.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	keep if chamber=="House"
	replace chamber = lower(chamber)
	gen term = ""
	replace term = "2003-2004" if regexm(date, "2003|2004")
	replace term = "2005-2006" if regexm(date, "2005|2006")	
	replace term = "2007-2008" if regexm(date, "2007|2008")
	replace term = "2009-2010" if regexm(date, "2009|2010")
	replace term = "2011-2012" if regexm(date, "2011|2012")
	replace term = "2013-2014" if regexm(date, "2013|2014")
	replace term = "2015-2016" if regexm(date, "2015|2016")	
	

	gen os_mergename = trim(lower(name))
	replace os_mergename = lower(os_mergename)
	replace os_mergename = subinstr(os_mergename,"’","",.)
	replace os_mergename = subinstr(os_mergename,"'","",.)
	replace os_mergename = subinstr(os_mergename,"-","",.)
	replace os_mergename = "king"	if os_mergename=="king s" & regexm(term, "2009")
	replace os_mergename = "johnson"	if os_mergename=="johnson r" & regexm(term, "2003")
	replace os_mergename = "may"	if os_mergename=="may m" & regexm(term, "2003|2005")
	replace os_mergename = "gardner r"	if os_mergename=="gardner b" & regexm(term, "2007|2009|2011")
	replace os_mergename = "mitchell"	if os_mergename=="mitchell v" & regexm(term, "2007")
	replace os_mergename = "schafer"	if os_mergename=="schafer s" & regexm(term, "2009|2011")
	replace os_mergename = "williams"	if os_mergename=="williams a" & regexm(term, "2011")		
	replace os_mergename = "bush"	if os_mergename=="mitsch bush" & regexm(term, "2013|2015")	
	replace os_mergename = "navarroratzlaff"	if os_mergename=="navarro" & regexm(term, "2013|2015")
	replace os_mergename = "neville"	if os_mergename=="neville p" & regexm(term, "2015")	
	replace os_mergename = "vanwinkle"	if os_mergename=="van winkle" & regexm(term, "2015")
	replace os_mergename = "hullinghorst"	if os_mergename=="speaker" & regexm(term, "2015")
	replace os_mergename = "ferrandino"	if os_mergename=="speaker" & regexm(term, "2013")
	replace os_mergename = "mcnulty"	if os_mergename=="speaker" & regexm(term, "2011")
	replace os_mergename = "carroll"	if os_mergename=="speaker" & regexm(term, "2009")
	replace os_mergename = "romanoff"	if os_mergename=="speaker" & regexm(term, "2005|2007")
	replace os_mergename = "spradley"	if os_mergename=="speaker" & regexm(term, "2003")
	
	egen vote_id = group(session chamber bill date url)
	gen x = 1
	bys vote_id : egen max = total(x)
	sort vote_id name
	by vote_id name: drop if _n>1
	drop x max
	
	merge m:1  os_mergename term using `tmp'		
	// 9 obs not merging
	drop if _merge==1
	drop _merge
	gen floorvotes = regexm(lower(vote), "y|n")
	replace vote = "1" if regexm( lower(vote), "y")
	replace vote = "-1" if regexm( lower(vote), "n")
	replace vote = "0" if vote!="1" & vote!="-1"
	destring vote, replace
	bys party vote_id : egen party_mean=mean(vote)
	gen party_vote = 0
	replace party_vote = 1 if party_mean>0
	replace party_vote = -1 if party_mean<0
	gen vote_party_line = vote==party_vote
	gen vote_against_party = vote==-party_vote
	bys vote_id: egen votemargin = mean(vote)
	gen close_floorvotes = floorvotes if abs(votemargin)<.1			
		
	
	*floor votes	
	save ../data_output/floorvotes_CO_2004_2016.dta, replace
project , creates(../data_output/floorvotes_CO_2004_2016.dta)		

	use ../data_output/floorvotes_CO_2004_2016.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum) *floorvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party  inoffice year  vtsh, by(CandId term )
	save ../data_output/total_floorvotes_CO_2004_2016.dta, replace	
project , creates(../data_output/total_floorvotes_CO_2004_2016.dta)		
	
	
	

	***CMT voting	
	
	use ../data_output/legislators_klarner.dta, clear
	gen lastname = regexs(1) if regexm(lower(CandName),"([^,]+), [^!]")
	gen firstname = regexs(1) if regexm(lower(CandName),"[^,]+, ([^ ])")
	keep if state=="CO" & chamber=="house"
	keep if regexm(term, "1999|2001|2003|2005|2007|2009|2011|2013|2015")
	gen os_mergename = lastname
	bys  os_mergename term: gen n = _n
	by  os_mergename term: egen mean_n=mean(n)
	replace os_mergename = lastname + " " + firstname+"."  if mean_n>1  //firstname  + " " +  lastname //firstname+ ". "
	drop n mean_n
	tempfile tmp
	save `tmp', replace
	
project , original(../data_input/CO/co_cmtvotes.csv)			
	import delimited using ../data_input/CO/co_cmtvotes.csv,  clear  delimiters(";") bindquotes(strict) encoding("utf-8") varnames(1)
	keep if chamber=="house"
	gen term = ""
	replace term = "1999-2000" if regexm(date, "1999|2000")	
	replace term = "2001-2002" if regexm(date, "2001|2002")
	replace term = "2003-2004" if regexm(date, "2003|2004")
	replace term = "2005-2006" if regexm(date, "2005|2006")	
	replace term = "2007-2008" if regexm(date, "2007|2008")
	replace term = "2009-2010" if regexm(date, "2009|2010")
	replace term = "2011-2012" if regexm(date, "2011|2012")
	replace term = "2013-2014" if regexm(date, "2013|2014")
	replace term = "2015-2016" if regexm(date, "2015|2016")

	gen os_mergename = trim(lower(name))
	replace os_mergename = lower(os_mergename)
	replace os_mergename = subinstr(os_mergename,"’","",.)
	replace os_mergename = subinstr(os_mergename,"'","",.)
	replace os_mergename = subinstr(os_mergename,"-","",.)	
	replace os_mergename = "johnson"	if os_mergename=="johnson r." & regexm(term, "2003")
	replace os_mergename = "may"	if os_mergename=="may m." & regexm(term, "2003|2005")
	replace os_mergename = "gardner r."	if os_mergename=="gardner b." & regexm(term, "2007|2009|2011")
	replace os_mergename = "mitchell"	if os_mergename=="mitchell v." & regexm(term, "2007")
	replace os_mergename = "schafer"	if os_mergename=="schafer s." & regexm(term, "2009|2011")
	replace os_mergename = "williams"	if os_mergename=="williams a." & regexm(term, "2011")
	replace os_mergename = "navarroratzlaff"	if os_mergename=="navarro" & regexm(term, "2013|2015")
	replace os_mergename = "bush"	if os_mergename=="mitsch bush" & regexm(term, "2013|2015")
	replace os_mergename = "neville"	if os_mergename=="neville p." & regexm(term, "2015")
	replace os_mergename = "vanwinkle"	if os_mergename=="van winkle" & regexm(term, "2015")
	
	merge m:1  os_mergename term using `tmp'	
	// 18 obs not merging
	drop if _merge==1
	drop _merge
	gen cmtvotes = regexm(lower(vote), "yes|no")
	replace vote = "1" if regexm( lower(vote), "yes")
	replace vote = "-1" if regexm( lower(vote), "no")
	replace vote = "0" if vote!="1" & vote!="-1"
	destring vote, replace
	egen vote_id = group(chamber location motion date url)
	bys party vote_id : egen party_mean=mean(vote)
	gen party_vote = 0
	replace party_vote = 1 if party_mean>0
	replace party_vote = -1 if party_mean<0
	gen vote_party_line = vote==party_vote
	gen vote_against_party = vote==-party_vote

	*cmt votes	
	save ../data_output/cmtvotes_CO_2000_2016.dta, replace	
project , creates(../data_output/cmtvotes_CO_2000_2016.dta)		
	
	use ../data_output/cmtvotes_CO_2000_2016.dta, clear
	collapse  (mean) vote_party_line vote_against_party (sum) cmtvotes (first) elections_* terms_in_office win TermLengthActual CandName district chamber state party  inoffice year  vtsh, by(CandId term )
	save ../data_output/total_cmtvotes_CO_2000_2016.dta, replace
project , creates(../data_output/total_cmtvotes_CO_2000_2016.dta)		
		
	

	
